import os
import sys
import django
import xlwt

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")
django.setup()

from core.tools.mysql_connect import SqlModel

"""
首先通过SalesSign找到推荐人
然后将推荐人和数据放到BaseOrder中
"""


class ExportData(SqlModel):
    def __init__(self):
        super(ExportData, self).__init__()

    def get_apply_info(self):
        sql = "SELECT  a.name, a.tel, s.create_time FROM `sales_man_single` AS s, `sales_man_applicantlist` AS a  where  s.`product_id` = 12 and s.`create_time` < '2018-12-01' and s.`create_time` >= '2018-11-01'  and s.applicant_id = a.id"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        result_json = []
        for i in result:
            d = dict()
            d["name"] = i[0]
            d["tel"] = i[1]
            d["create_time"] = i[2].strftime("%Y-%m-%d")
            result_json.append(d)
        return result_json

    def export_data(self):
        book = xlwt.Workbook()  # 创建excel对象
        # 写表头
        result_json = self.get_apply_info()
        leader_row = 0

        sheet1 = book.add_sheet('KLB1')  # 添加一个表
        # 写表头
        header_line = ["姓名", "手机号", "申请时间"]
        for header in header_line:
            sheet1.write(0, leader_row, header)
            leader_row += 1
        row1 = 1
        for i in result_json:
            sheet1.write(row1, 0, i.get("name"))
            sheet1.write(row1, 1, i.get("tel"))
            sheet1.write(row1, 2, i.get("create_time"))
            row1 += 1
        book.save("C_zy.xls")


def write_data():
    export_data = ExportData()
    export_data.export_data()


if __name__ == '__main__':
    write_data()
